<?php (defined('BASEPATH')) OR exit('No direct script access allowed');

class Product_model  extends MY_Model  {

    public $table = 'products';

	public function __construct() {
		$this->class_name = get_class ( $this );
		parent::__construct( $this->class_name, $this->table );
		parent::init( $this->class_name, $this->table );
	}
	
    /**
     * Get row from DB given by sku
     *
     * @access public
     * @param string $sku
     * @param int $lang
     * @param int $imported
     * @return object
     */
    public function get_by_sku($sku, $lang = 0, $imported = 0) {
    	$where = 'sku = "' . $sku . '"';
    	if ($lang) {
    		$where .= ' AND language_id = ' . (int) $lang;
    	}
    	
        if ($imported) {
    		$where .= ' AND imported = ' . (int) $imported;
    	}    	

        $result =  $this->db->select()->from($this->table)->where($where)->get()->row();
        $obj = FALSE;
        if ($result) {
            $obj = $this->from_array((array)$result);
        }
        return $obj;
    }
    /**
     * Get row from DB given by sku
     *
     * @access public
     * @param string $sku
     * @param int $lang
     * @param int $imported
     * @return object
     */
    public function get_by_sku_pd($sku, $lang = 0, $imported = 0) {
    	$where = 'sku_pd = "' . $sku . '"';
    	if ($lang) {
    		$where .= ' AND language_id = ' . (int) $lang;
    	}
    	
        if ($imported) {
    		$where .= ' AND imported = ' . (int) $imported;
    	}    	

        $result =  $this->db->select()->from($this->table)->where($where)->get()->row();
        $obj = FALSE;
        if ($result) {
            $obj = $this->from_array((array)$result);
        }
        return $obj;
    }

    public function getAll($filter = array(), $notFilter = array()){
  
    	if (isset($filter ['field']) and strlen($filter ['field'])) {
            $this->db->select($filter ['field']);
        } else {
            $this->db->select($this->table . ".*, images.url");
        }
        
        $this->db->join('images','images.product_id = products.id AND (images.sort = 1 OR images.sort = 0 OR images.sort IS NULL)','left');
        
    	if (isset($filter['id']) AND strlen($filter['id'])) {
            $this->db->where($this->table . '.id', $filter['id']);
        }
    	
        if (isset($filter['id_in']) AND strlen($filter['id_in'])) {
            $this->db->where($this->table . '.id IN (' . $filter['id_in'] . ')');
        }
        
        if (isset($filter['language_id']) AND strlen($filter['language_id'])) {
            $this->db->where($this->table . '.language_id', $filter['language_id']);
        }
        if (isset($filter['sku_left']) AND strlen($filter['sku_left'])) {
            $this->db->where($this->table . '.sku LIKE "' . $filter['sku_left'] . '%"');
        }
        if (isset($filter['promotion']) AND strlen($filter['promotion'])) {
        	$this->db->join('images as main_images', 'products.parent_id = main_images.product_id AND (main_images.sort = 1 OR main_images.sort = 0)','LEFT');
        	$this->db->join('products as p ',' products.id = p.parent_id ','LEFT');
        	$this->db->select('main_images.url as main_image_url, MIN(IF(p.special_price != 0,p.special_price,p.price )) as min_price ');
            $this->db->where($this->table . '.promotion', $filter['promotion']);
            $this->db->group_by('p.parent_id');
        }
        
        if (isset($filter['sku']) AND strlen($filter['sku'])) {
            $this->db->where($this->table . '.sku', $filter['sku']);
        }  
        
        if (isset($filter['sku_pd']) AND strlen($filter['sku_pd'])) {
            $this->db->where($this->table . '.sku_pd', $filter['sku_pd']);
        }  
        
        if (isset($filter['sku_like']) AND strlen($filter['sku_like'])) {
            $this->db->like($this->table . '.sku', $filter['sku_like']);
        }  

        if (isset($notFilter['language_id']) AND strlen($notFilter['language_id'])) {
            $this->db->where($this->table . '.language_id != ', $notFilter['language_id']);
        }         
        
        if (isset($filter['parent_id']) AND strlen($filter['parent_id'])) {
            $this->db->where($this->table . '.parent_id', $filter['parent_id']);
        } 
                
        if (isset($filter['color']) AND strlen($filter['color'])) {
            $this->db->where($this->table . '.colour', $filter['color']);
        }         
        if (isset($filter['metal']) AND strlen($filter['metal'])) {
            $this->db->where('UPPER(' . $this->table . '.metal)', strtoupper($filter['metal']));
        }         
        
        if (isset($filter['clarity']) AND strlen($filter['clarity'])) {
            $this->db->where($this->table . '.clarity', $filter['clarity']);
        }         

        $order = $this->table . ".id";
        $dir = "asc";
        if (isset($filter['order']) AND strlen($filter['order'])){
        	$order = $filter['order'];
        }

        if (isset($filter['dir']) AND strlen($filter['dir'])){
        	$dir = $filter['dir'];
        }
        
        $this->db->order_by($order,$dir);
        
        if (isset($filter['group_by']) AND strlen($filter['group_by'])) {
            $this->db->group_by($filter['group_by']);
        }
        
        
        if (isset($filter['from']) AND isset($filter['to'])){
        	$query = $this->db->get($this->table,$filter['to'],$filter['from']);
        } else {
        	$query = $this->db->get($this->table);
        }
      
        return $query;
    }
    
  	public function getProductList($filter = array(),$count = false){
  		//print_r($filter);
  		//die();
  		
  		if (!$count){
    		$sql = "SELECT * FROM (";   
  		} else {
  			$sql = "SELECT COUNT(id) as cnt FROM (";
  		}
  		
  		
  		$union1 = "(SELECT `products`.*, `images`.`url`";

  		if (isset($filter['related_to']) AND strlen($filter['related_to'])) {
  			$union1 .= " FROM product_category_mn
  					LEFT JOIN `category` ON `product_category_mn`.`foreign_id` = `category`.`id` 
	  				LEFT JOIN product_category_mn AS related_cat ON `related_cat`.`foreign_id` = `category`.`related_category`
	  				LEFT JOIN products ON products.id = related_cat.local_id ";
  		} else {  		
  			$union1 .= " FROM products 
  				LEFT JOIN `products` as main_product ON `products`.`parent_id` = `main_product`.`id`
  				LEFT JOIN `images` ON `images`.`product_id` = `products`.`id` AND (images.sort = 1 OR images.sort = 0 OR images.sort IS NULL) 
  				LEFT JOIN `product_category_mn` ON `product_category_mn`.`local_id` = `main_product`.`id`
  				LEFT JOIN `category` ON `product_category_mn`.`foreign_id` = `category`.`id`  ";
  		}
  		$union1 .= "WHERE 
				products.parent_id = 0 AND products.status = 1 ";
  		$union1 .= $this->getFilterSql($filter);
  		
  		$union1 .= "GROUP BY `products`.`id`";
  		
  		$union1 .= ")";
  		
  		$union2 = "(SELECT `main_product`.*, `images`.`url`";

  		if (isset($filter['related_to']) AND strlen($filter['related_to'])) {
  			$union2 .= " FROM product_category_mn
  					LEFT JOIN `category` ON `product_category_mn`.`foreign_id` = `category`.`id` 
  					LEFT JOIN product_category_mn AS related_cat ON `related_cat`.`foreign_id` = `category`.`related_category`
  					LEFT JOIN products ON products.id = related_cat.local_id ";
  		} else {  		
  			$union2 .= " FROM products 
  				LEFT JOIN `products` as main_product ON `products`.`parent_id` = `main_product`.`id` 
				LEFT JOIN `images` ON `images`.`product_id` = `main_product`.`id` AND (images.sort = 1 OR images.sort = 0) 
  				LEFT JOIN `product_category_mn` ON `product_category_mn`.`local_id` = `main_product`.`id`
  				LEFT JOIN `category` ON `product_category_mn`.`foreign_id` = `category`.`id`  ";
  		}

  		$union2 .= "
				WHERE 
				products.parent_id != 0 AND products.status = 1 ";
  		$union2 .= $this->getFilterSql($filter);
  		
  		$union2 .= "GROUP BY `products`.`parent_id`";
  		
  		$sql .= $union1 . " UNION " .$union2;
  		
  		$sql .= ")) as result";
  		
  		if (!$count){
        	$sql .= " GROUP BY result.id ";
        }
    	
        if (isset($filter['order_by']) AND isset($filter['order_dir'])){
        	$sql .= " ORDER BY result." . $filter['order_by'] . " " . $filter['order_dir'];
        }
        
        if (isset($filter['from']) AND isset($filter['to'])){
        	$sql .= " LIMIT " . $filter['from'] . "," . $filter['to'];
        } 
    
        $query = $this->db->query($sql);

    	return $query;
    }
    
    private function getFilterSql($filter = array()){
    	$sql = '';
    	
    	if (isset($filter['language_id']) AND strlen($filter['language_id'])) {
            $sql .= ' AND products.language_id = "' . $filter['language_id'] . '"';
        }
    	
    	if (isset($filter['category']) AND strlen($filter['category']) AND !isset($filter['style'])) {
            $sql .= ' AND category.id = "' . $filter['category'] . '"';
        }
    	if (isset($filter['related_to']) AND strlen($filter['related_to'])) {
            $sql .= ' AND product_category_mn.local_id = "' . $filter['related_to'] . '"';
        }
        
        if (isset($filter['category_parent']) AND strlen($filter['category_parent'])) {
            $sql .= ' AND category.parent_id = "' . $filter['category_parent'] . "'";
        }
        
    	if (isset($filter['original_id']) AND strlen($filter['original_id'])) {
             $sql .= ' AND category.original_id = "' . $filter['original_id'] . '"';
        }
    	
        if (isset($filter['style']) AND strlen($filter['style'])) {
             $sql .= ' AND category.name = "' . $filter['style'] . '"
             	AND category.parent_id = "' . $filter['category'] .  '"';
        }
        
        if (isset($filter['price_more']) AND strlen($filter['price_more'])) {
            $sql .= ' AND ((products.price/' . $filter['brit_vat'] . ')-(1.3*' . $filter['current_vat'] . ')) >="' . $filter['price_more'] . '"';
        }
        
  		if (isset($filter['price_less']) AND strlen($filter['price_less'])) {
            $sql .= ' AND ((products.price/' . $filter['brit_vat'] . ')-(1.3*' . $filter['current_vat'] . ')) <="' . $filter['price_less'] . '"';
        }
        
        if ((isset($filter['price_less']) AND strlen($filter['price_less'])) || (isset($filter['price_more']) AND strlen($filter['price_more']))){
        	$sql .= ' AND products.parent_id != 0 ';
        }
  		
        if (isset($filter['size_more']) AND strlen($filter['size_more'])) {
            $sql .= ' AND products.carat_weight >= "' . $filter['size_more'] . '"';
        }
        
  		if (isset($filter['size_less']) AND strlen($filter['size_less'])) {
           $sql .= ' AND products.carat_weight <= "' . $filter['size_less'] . '"';
        }
        
  		if (isset($filter['metal']) AND strlen($filter['metal'])) {
            $sql .= ' AND UPPER(products.metal) = "' . strtoupper($filter['metal']) . '"';
        }
        
  		if (isset($filter['gem']) AND strlen($filter['gem'])) {
            $sql .= ' AND products.name LIKE "%' . $filter['gem'] .'%"';
        }
  		  		
        if (isset($filter['shape']) AND strlen($filter['shape'])) {
            $sql .= ' AND UPPER(products.shape) = "' . strtoupper($filter['shape']) . '"';
        }
        
        if (isset($filter['quality']) AND strlen($filter['quality'])) {
            $sql .= ' AND UPPER(products.clarity) = "' . strtoupper($filter['quality']) . '"';
        }
        
        if (isset($filter['color']) AND strlen($filter['color'])) {
            $sql .= ' AND UPPER(products.colour) = "' . strtoupper($filter['color']) . '"';
        }
        
        if (isset($filter['setting']) AND strlen($filter['setting'])) {
            $sql .= ' AND UPPER(products.clarity) = "' . strtoupper($filter['setting']) . '"';
        }    
        
        if (isset($filter['promotion']) AND strlen($filter['promotion'])) {
            $sql .= ' AND products.promotion = "' . strtoupper($filter['promotion']) . '"';
        }    

    	 if (isset($filter['search']) AND isset($filter['search'])){
        	$sql .= " AND (products.name LIKE '%" . $filter['search'] . "%' 
        					OR products.description LIKE '%" . $filter['search'] . "%'
        					OR products.sku LIKE '%" . $filter['search'] . "%'
        					OR category.name LIKE '%" . $filter['search'] . "%')";
        }
        
    	return $sql;    	
    }
    
	public function getColorsByProductId($productId){
    	$this->db->select($this->table . ".colour");
     
        $this->db->where('parent_id', $productId);

        $order = $this->table . ".colour";
        $dir = "asc";
               
        $this->db->order_by($order,$dir);
        $this->db->group_by($this->table . ".colour");
        
        $query = $this->db->get($this->table);
         
        return $query;
    }
	
    public function getClaritiesByProductId($productId){
    	$this->db->select($this->table . ".clarity");
     
        $this->db->where('parent_id', $productId);
        $this->db->where('clarity != ""');

        $order = $this->table . ".clarity";
        $dir = "asc";
               
        $this->db->order_by($order,$dir);
        $this->db->group_by($this->table . ".clarity");
        
        $query = $this->db->get($this->table);
         
        return $query;
    }

    public function getMetlasByProductId($productId){
    	$this->db->select($this->table . ".metal");
     
        $this->db->where('parent_id', $productId);

        $order = $this->table . ".metal";
        $dir = "asc";
               
        $this->db->order_by($order,$dir);
        $this->db->group_by($this->table . ".metal");
        
        $query = $this->db->get($this->table);
         
        return $query;
    }

    public function getRingSizesByProductId($productId,$field,$gender = ''){
    	$this->db->select("size." . $field . ' as size_field');
     
    	$this->db->join("size","product_size_mn.foreign_id = size.id");
    	
        $this->db->where('product_size_mn.local_id', $productId);
        
        if (strlen($gender)){
        	$this->db->where("size.gender",$gender);
        }
        
        $query = $this->db->get('product_size_mn');
       //  echo $this->db->last_query();
        return $query;
    }
    
    function addRating($productId,$rating){
    	$sql = "UPDATE products SET rating = ((rating*rate_count + " . $rating . ")/(rate_count+1)),
				rate_count = rate_count+1
    		WHERE products.id = " . $productId;
    	
    	$this->db->query($sql);
    }
    
    function getRelatedProducts($prodId){
    	$this->db->select(
    					$this->table . '.id, ' .
    					$this->table . '.name, ' .
    					$this->table . '.price, ' .
    					$this->table . '.special_price, ' .
    					'related_cat.foreign_id as rel_cat_id,' .
    					'images.url, ' .
    					'main_images.url as main_image_url' );
    	
    	$this->db->where('product_category_mn.local_id',$prodId);
    	$this->db->join('category','category.id = product_category_mn.foreign_id');
    	$this->db->join('product_category_mn AS related_cat','related_cat.foreign_id = category.related_category');
    	$this->db->join($this->table,$this->table. '.id = related_cat.local_id');
    	$this->db->join('images as main_images', 'products.parent_id = main_images.product_id AND (main_images.sort = 1 OR main_images.sort = 0)','LEFT');
    	$this->db->join('images','images.product_id = products.id AND (images.sort = 1 OR images.sort = 0)','left');
    	
    	$this->db->group_by($this->table . '.id');
    	
    	$query = $this->db->get('product_category_mn');
    
    	return $query;
    }
    
    function getMetlasByProductSku($sku){
    	$this->db->select($this->table . ".metal");
     
        $this->db->where('parent_id', 0);
        $this->db->where('language_id', $this->config->item('lang_obj')->id);
        $this->db->like('sku', $sku);

        $order = $this->table . ".metal";
        $dir = "asc";
               
        $this->db->order_by($order,$dir);
        $this->db->group_by($this->table . ".metal");
        
        $query = $this->db->get($this->table);
         
        return $query;
    }
    
    function getProductCategory($filter){
    	$this->db->select('category.id, category.name');
    	$this->db->from('product_category_mn');
    	$this->db->join('category','category.id = product_category_mn.foreign_id','LEFT');
    	
    	if (isset($filter['product_id'])){
    		$this->db->where('product_category_mn.local_id',$filter['product_id']);
    	}
    	if (isset($filter['category_id'])){
    		$this->db->where('category.parent_id',$filter['category_id']);
    	}
    	
    	$this->db->order_by('category.order','ASC');
    	$this->db->limit(1);
    	
    	$query = $this->db->get();
   
    	return $query;
    }
    
    function getProviderBonus($prodId){
    	$this->db->select('promotion_value')->from('product_category_mn');
    	
    	$this->db->join('category','category.id = product_category_mn.foreign_id');
    	$this->db->where('product_category_mn.local_id = ' . $prodId);
    	$this->db->order_by('promotion_value','DESC');
    	$this->db->limit(1);
    	
    	$result = $this->db->get()->result();

    	if (!empty($result)){
    		return $result[0]->promotion_value;
    	} else {
    		return 0;
    	}
    }

}